<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN">
<html>
<head>
<title>How to identify DB objects in your sql</title>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <meta name="generator" content="Doc-O-Matic" />
    <meta http-equiv="Content-Style-Type" content="text/css" />
    <link rel="STYLESHEET" href="default.css" type="text/css" />

<script type="text/javascript" src="scripts.js"></script>
</head>
<body class="Element700" onload="onBodyLoadEx('frames.html', 'topic', 'How_to_identify_DB_objects_in_your_sql.html');" onmousedown="onBodyMouseDown();">

<!-- Begin Popups -->

<!-- End Popups -->

<!-- Begin Page Header -->
<div class="Element710" id="areafixed">
<div class="Element94">
<em>You are here:</em> Using General SQL Parser &gt; <a href="How_to_identify_DB_objects_in_your_sql.html" target="topic">How to identify DB objects in your sql</a></div>
<div class="Element92">
<table width="100%" cellspacing="0" cellpadding="0">
<tr><td width="50%">
<div class="Element2">
<a href="contents.html" target="tocidx"><img src="btn_globals_contents_midblue.gif" border="0" alt="Contents" title="Contents" onmouseover="switchImage(this, 'btn_globals_contents_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_contents_midblue.gif');"></a><a href="idx.html" target="tocidx"><img src="btn_globals_index_midblue.gif" border="0" alt="Index" title="Index" onmouseover="switchImage(this, 'btn_globals_index_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_index_midblue.gif');"></a><a href="index.html" target="topic"><img src="btn_globals_home_midblue.gif" border="0" alt="Home" title="Home" onmouseover="switchImage(this, 'btn_globals_home_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_home_midblue.gif');"></a></div>
</td><td width="50%">
<div class="Element90">
<a href="Visitor_pattern_used_in_General_SQL_Parser.html" target="topic"><img src="btn_prev_midblue.gif" border="0" alt="Previous" title="Previous" onmouseover="switchImage(this, 'btn_prev_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_prev_midblue.gif');"></a><img src="btn_up_d.gif" border="0" alt="Up" title="Up"><a href="How_to_use_expression.html" target="topic"><img src="btn_next_midblue.gif" border="0" alt="Next" title="Next" onmouseover="switchImage(this, 'btn_next_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_next_midblue.gif');"></a></div>
</td></tr></table><div class="Element5">
How to identify DB objects in your sql</div>
</div>
</div>

<!-- End Page Header -->

<!-- Begin Client Area -->
<div class="Element720" id="areascroll">
<div class="Element721">

<!-- Begin Page Content -->
<div class="Element58">
<a name="Description"></a><div class="Element11">
<div class="Element10">
<div class="Element15">
How to identify database objects in your sql</div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
There are several ways to identify database object in sql statement.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
<strong>1. Iterate source tokens in sql statement,and check <a href="TSourceToken_DBObjType.html" target="topic">TSourceToken.DBObjType</a> to find out database object in this sql</strong>.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
This is the easiest way to find out database objects in sql statement after parse sql. You may read <a href="Source_token_and_database_object.html" target="topic">relationship between source token</a>&nbsp;</p>
<p class="Element10">
<a href="Source_token_and_database_object.html" target="topic">and database object</a> first to understand how this works.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
You can iterate source tokens in several ways.&nbsp;</p>
<p class="Element10">
1.1 Iterate source tokens via TGSqlParser.SourceTokenList&nbsp;</p>
<p class="Element10">
All source tokens in input sql script are in this source token list, no matter how may sql statements in the input sql script.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
1.2 Iterate source tokens via <a href="TCustomSqlStatement_SourceTokenList.html" target="topic">TCustomSqlStatement.SourceTokenList</a>&nbsp;</p>
<p class="Element10">
If you want to visit source token per sql statement, this is preferred.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
1.3 Iterate source tokens inside a sql clause, such as where clause, join clause.&nbsp;</p>
<p class="Element10">
You can visit source tokens only inside a sql clause. First, you need to find begin source token, and end source token of this sql clause,this can be done by using StartToken and EndToken property of <a href="TSourceToken.html" target="topic">TSourceToken</a>, after that you can iterate source tokens between this 2 tokens in <a href="TCustomSqlStatement_SourceTokenList.html" target="topic">TCustomSqlStatement.SourceTokenList</a> with range from StartToken.posinlist to EndToken.posinlist.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
You can use this method to iterate source tokens in any parse tree nodes inherited from <a href="TLz_Node.html" target="topic">TLz_Node Class</a>, such as Where clause,join clause.During visiting source tokens, you can find out any database objects by checking <strong><a href="TSourceToken_DBObjType.html" target="topic">TSourceToken.DBObjType</a></strong> .&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
<strong>2. Using <a href="TCustomSqlStatement_Fields.html" target="topic">TCustomSqlStatement.Fields</a> and <a href="TCustomSqlStatement_Tables.html" target="topic">TCustomSqlStatement.Tables</a> to access fields and tables information in sql.</strong>&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Only fields in select list and tables in from clause are listed in these 2 properties.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Fields in <a href="TCustomSqlStatement_Fields.html" target="topic">TCustomSqlStatement.Fields</a> may not connect to columns in database directly, take this sql for example:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">select f1+f2
from t1</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
there is one field in select list: f1+f2, which includes 2 columns f1 and f2. Please check <a href="TLzField_FieldType.html" target="topic">TLzField.FieldType</a> for type of field.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Tables in <a href="TCustomSqlStatement_Tables.html" target="topic">TCustomSqlStatement.Tables</a> may not connect to tables in database directly, take this sql for example:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">select f1
from (select f2 from t1)</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Table in from clause is a subquery which is not a physical table in database.Please check <a href="TLzTable_TableType.html" target="topic">TLzTable.TableType</a> for type of table.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
<strong>3. Using <span class="Element145">TCustomSqlStatement.OnFieldToken Even</span>t and <span class="Element145">TCustomSqlStatement.OnTableToken Event</span> to access fields and tables information in sql.</strong>&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
During parsing sql statement, <span class="Element145">TCustomSqlStatement.OnFieldToken Even</span>t was fired when a source token represents a column was found, and <span class="Element145">TCustomSqlStatement.OnTableToken Event</span> was fired when a source token represents a table wasa found.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
You can find all columns and tables in sql statement that not limited in select list and from clause. But only column and table will be found, not other database object such as function will be found in current version. This limitation will be removed when more database objects event were introduced in new version.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
<strong>4. Using Database object tokens property in <a href="TCustomSqlStatement.html" target="topic">TCustomSqlStatement</a> such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens.</strong>&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Database object tokens in <a href="TCustomSqlStatement.html" target="topic">TCustomSqlStatement</a> such as ServerTokens,DatabaseTokens,SchemaTokens,TableTokens store all original information about different database objects in sql statement. You can find everything you need in those properties.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Meanwhile, information in those properties are grouped by each sql statement, and not sorted, same object may occurs serval time. Sometimes, we want to access thsse database objects in a script level, grouped by server,database,schema with sourted information.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
So, we prepare all these raw information in <a href="TCustomSqlStatement.html" target="topic">TCustomSqlStatement</a> into a new class: <a href="TLzDBObject.html" target="topic">TLzDBObject</a>, along with <a href="TLzDBObjectList.html" target="topic">TLzDBObjectList</a>, after that you have a more convenience way to access database object in sql scripts.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
For how to achieve this, please check this demo:&nbsp;</p>
<p class="Element10">
<a href="http://www.sqlparser.com/dl/samples/getdbobject.zip">http://www.sqlparser.com/dl/samples/getdbobject.zip</a>&nbsp;</p></div>
</div>
</div>
<!-- End Page Content -->

<!-- Begin Page Footer -->
<div class="Element93">
<table width="100%" cellspacing="0" cellpadding="0">
<tr><td width="100%">
<div class="Element3">
Gudu software http://www.sqlparser.com</div>
</td></tr><tr><td width="100%">
<div class="Element97">
Send <a href="#" onclick="sendFeedback('info@sqlparser.com', 'Documentation Feedback', 'Project: General SQL Parser Help file%0ATopic ID: How to identify DB objects in your sql%0ATitle: How to identify DB objects in your sql');">comments</a> about this topic.</div>
</td></tr></table></div>

<!-- End Page Footer -->
</div>
</div>

<!-- End Client Area -->
</body></html>